package com.yuuzy.dao;

import com.alibaba.druid.util.StringUtils;
import com.yuuzy.model.Buy_Insurance;
import com.yuuzy.model.Client;
import com.yuuzy.model.Insurance;
import com.yuuzy.utils.DBUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

public class Buy_InsuranceDAO extends BasicDAO<Buy_Insurance> {
    private QueryRunner qr = new QueryRunner();

    /**
     * 查询购买保险的数据
     *
     * @param buy_insurance 待查询的购买保险类（传入产品id和客户id，不传入默认查询全部记录）
     * @return 查询到的购买保险类集
     */
    public List<Buy_Insurance> queryBuy_Insurance(Buy_Insurance buy_insurance) {
        StringBuilder sb = new StringBuilder("select * from buy_insurance");
        if (buy_insurance.getInsurance_id() != null && buy_insurance.getClient_id() != null) {
            //根据客户id和保险id查询
            sb.append(" and insurance_id = ").append(buy_insurance.getInsurance_id());
            sb.append(" and client_id = ").append(buy_insurance.getClient_id());
        }
        String sql = sb.toString().replaceFirst("and", "where");
        return queryMulti(sql, Buy_Insurance.class);
    }

    /**
     * 增加购买保险记录
     *
     * @param buy_insurance 待传入的购买保险实体
     * @return 受影响的行数
     */
    public int insertBuy_Insurance(Buy_Insurance buy_insurance) {
        String sql = "INSERT INTO finance.buy_insurance(insurance_id,client_id,buy_insurance_time,buy_insurance_assetstatus) VALUES (?,?,?,?)";
        return update(sql, buy_insurance.getInsurance_id(), buy_insurance.getClient_id(), buy_insurance.getBuy_insurance_time(), buy_insurance.getBuy_insurance_assetstatus());
    }

    /**
     * 删除购买保险的数据
     *
     * @param buy_insurance 待删除的购买保险类（必须包含客户id和保险id）
     * @return 受影响的行数
     */
    public int deleteBuy_Insurance(Buy_Insurance buy_insurance) {
        StringBuilder sb = new StringBuilder("DELETE FROM buy_insurance");
        if (buy_insurance.getClient_id() != null && buy_insurance.getInsurance_id() != null) {
            sb.append(" and client_id = ").append(buy_insurance.getClient_id());
            sb.append(" and buy_insurance_id = ").append(buy_insurance.getInsurance_id());
        }
        String sql = sb.toString().replaceFirst("and", "where");
        return update(sql);
    }

    /**
     * 更新购买保险记录
     *
     * @param buy_insurance 待更新的购买保险类
     * @return 受影响的行数
     */
    public int updateBuy_Insurance(Buy_Insurance buy_insurance) {
        //客户id和保险id不能更改
        StringBuilder sb = new StringBuilder("UPDATE buy_insurance SET");
        if (buy_insurance.getBuy_insurance_time() != null) {
            //如果购买日期字段存在
            sb.append(" ,buy_insurance_time = '").append(buy_insurance.getBuy_insurance_time()).append("'");
        }
        if (!StringUtils.isEmpty(buy_insurance.getBuy_insurance_assetstatus())) {
            //如果状态字段存在
            sb.append(" ,buy_insurance_assetstatus = '").append(buy_insurance.getBuy_insurance_assetstatus()).append("'");
        }

        String sql = sb.append(" WHERE client_id = ? and insurance_id = ?").toString().replaceFirst(",", "");
        return update(sql, buy_insurance.getClient_id(), buy_insurance.getInsurance_id());
    }

    /*
    购买保险
    设计思路：
    传入一个客户类，必须包含用户名和密码
    传入一个保险类，必须包含购买的保险id
    返回一个Buy_Insurance类

    （1）调用ClientDAO中的查询方法，获得完整的客户对象
    （2）调用InsuranceDAO中的查询方法，获得完整的保险对象
    （3）先判断remain = (账户余额-保险金额）是否大于0，若小于0，结束进程,返回null
    （4）若大于0，先查找表中是否有此类购买保险对象的记录，若有没有，则：
    （4.1）创建一个Buy_Insurance类，将基本信息存入，并执行以下事务：
        向buy_insurance表中增加一条记录
        修改客户账号内余额
        结束事务，返回创建好的Buy_Insurance类
     （4.2）若已存在此类保险对象的记录，无须再次购买，返回null
     */

    /**
     * @param client    客户类
     * @param insurance 客户拟购买的保险类
     * @return 购买保险类
     */
    public Buy_Insurance buyInsurance(Client client, Insurance insurance) {
        ClientDAO clientDAO = new ClientDAO();
        //（1）调用ClientDAO中的查询方法，获得完整的客户对象
        List<Client> clientList = clientDAO.queryClient(client);
        client = clientList.get(0);//去除第一个客户，也只可能有一个客户
        //（2）调用InsuranceDAO中的查询方法，获得完整的保险对象
        List<Insurance> insuranceList = new InsuranceDAO().queryInsurance(insurance);
        insurance = insuranceList.get(0);//取出第一项保险，也只可能有一项保险
        //（3）先判断remain = （客户余额-保险金额）是否大于0
        double remain = (client.getClient_balance()) - (insurance.getInsurance_amount());

        if (remain < 0) {
            //余额小于0，购买失败
            System.out.println("余额不足");
            return null;
        }
        //（4）若大于0，先查找表中是否有此类保险对象的记录
        client.setClient_balance((double) remain);//设置购买后的余额
        List<Buy_Insurance> buy_insurances = queryBuy_Insurance(new Buy_Insurance(insurance.getInsurance_id(), client.getClient_id()));
        Connection conn = null;
        Buy_Insurance buyInsurance = null;
        //执行（4.1）的事务
        //（4.1）创建一个Buy_Insurance类，将基本信息存入，并执行以下事务：
        //        向buy_insurance表中增加一条记录
        //        修改客户账户内余额
        //        结束事务，返回创建好的Buy_Insurance类
        if (buy_insurances.isEmpty()) {
            //客户没有购买过该产品
            try {
                conn = DBUtils.getConnection();
                //开始事务
                conn.setAutoCommit(false);
                buyInsurance = new Buy_Insurance(insurance.getInsurance_id(), client.getClient_id(), new Date(new java.util.Date().getTime()), "可用");//默认数据
                int flag1 = qr.update(conn, "INSERT INTO finance.buy_insurance(insurance_id,client_id,buy_insurance_time,buy_insurance_assetstatus) VALUES (?,?,?,?)", buyInsurance.getInsurance_id(), buyInsurance.getClient_id(), buyInsurance.getBuy_insurance_time(), buyInsurance.getBuy_insurance_assetstatus());
                int flag2 = qr.update(conn, "UPDATE client SET client_balance=? where client_id=?", client.getClient_balance(), client.getClient_id());
                if (flag1 > 0 && flag2 > 0) {
                    //两个操作都成功
                    DbUtils.commitAndCloseQuietly(conn);//提交并关闭连接
                    return buyInsurance;
                } else {
                    //有一个操作不成功
                    DbUtils.rollbackAndCloseQuietly(conn);//回滚并关闭连接
                    System.out.println("操作失败");
                    return null;
                }
            } catch (SQLException e) {
                DbUtils.rollbackAndCloseQuietly(conn);
                throw new RuntimeException(e);
            }
        }
        //（4.2）若已存在此类保险对象的记录，无须再次购买，返回null
        System.out.println("已购买");
        return null;//客户购买过该产品，无须再次购买
    }
}

